{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Collecting tushare\n",
      "  Using cached https://pypi.tuna.tsinghua.edu.cn/packages/7e/3f/5149c21cb222c768c6c74526fc69183c659f6574d93d5d147c167054af56/tushare-1.4.18-py3-none-any.whl (142 kB)\n",
      "Requirement already satisfied: requests in d:\\anaconda\\lib\\site-packages (2.32.2)\n",
      "Requirement already satisfied: pandas in d:\\anaconda\\lib\\site-packages (from tushare) (2.2.2)\n",
      "Requirement already satisfied: lxml in d:\\anaconda\\lib\\site-packages (from tushare) (5.2.1)\n",
      "Collecting simplejson (from tushare)\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/fe/68/9f0e5df0651cb79ef83cba1378765a00ee8038e6201cc82b8e7178a7778e/simplejson-3.19.3-cp312-cp312-win_amd64.whl (75 kB)\n",
      "     ---------------------------------------- 0.0/75.6 kB ? eta -:--:--\n",
      "     --------------- ---------------------- 30.7/75.6 kB 660.6 kB/s eta 0:00:01\n",
      "     -------------------------------------- 75.6/75.6 kB 837.9 kB/s eta 0:00:00\n",
      "Collecting bs4 (from tushare)\n",
      "  Using cached https://pypi.tuna.tsinghua.edu.cn/packages/51/bb/bf7aab772a159614954d84aa832c129624ba6c32faa559dfb200a534e50b/bs4-0.0.2-py2.py3-none-any.whl (1.2 kB)\n",
      "Requirement already satisfied: websocket-client>=0.57.0 in d:\\anaconda\\lib\\site-packages (from tushare) (1.8.0)\n",
      "Requirement already satisfied: tqdm in d:\\anaconda\\lib\\site-packages (from tushare) (4.66.4)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in d:\\anaconda\\lib\\site-packages (from requests) (2.0.4)\n",
      "Requirement already satisfied: idna<4,>=2.5 in d:\\anaconda\\lib\\site-packages (from requests) (3.7)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in d:\\anaconda\\lib\\site-packages (from requests) (2.2.2)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in d:\\anaconda\\lib\\site-packages (from requests) (2024.6.2)\n",
      "Requirement already satisfied: beautifulsoup4 in d:\\anaconda\\lib\\site-packages (from bs4->tushare) (4.12.3)\n",
      "Requirement already satisfied: numpy>=1.26.0 in d:\\anaconda\\lib\\site-packages (from pandas->tushare) (1.26.4)\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in d:\\anaconda\\lib\\site-packages (from pandas->tushare) (2.9.0.post0)\n",
      "Requirement already satisfied: pytz>=2020.1 in d:\\anaconda\\lib\\site-packages (from pandas->tushare) (2024.1)\n",
      "Requirement already satisfied: tzdata>=2022.7 in d:\\anaconda\\lib\\site-packages (from pandas->tushare) (2023.3)\n",
      "Requirement already satisfied: colorama in d:\\anaconda\\lib\\site-packages (from tqdm->tushare) (0.4.6)\n",
      "Requirement already satisfied: six>=1.5 in d:\\anaconda\\lib\\site-packages (from python-dateutil>=2.8.2->pandas->tushare) (1.16.0)\n",
      "Requirement already satisfied: soupsieve>1.2 in d:\\anaconda\\lib\\site-packages (from beautifulsoup4->bs4->tushare) (2.5)\n",
      "Installing collected packages: simplejson, bs4, tushare\n",
      "Successfully installed bs4-0.0.2 simplejson-3.19.3 tushare-1.4.18\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install tushare -i https://pypi.tuna.tsinghua.edu.cn/simple requests\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tushare as ts\n",
    "import MySQLdb as mdb\n",
    "\n",
    "import matplotlib\n",
    "matplotlib.use(\"TkAgg\")\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from matplotlib.collections import LineCollection\n",
    "\n",
    "import pandas as pd\n",
    "from sklearn import cluster,covariance,manifold\n",
    "\n",
    "from matplotlib.font_manager import FontProperties\n",
    "\n",
    "\n",
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api('579f438eb79d5ee41e68586e4adf0dfc11edb58d68b6843e9d843518')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5395 entries, 0 to 5394\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   ts_code       5395 non-null   object\n",
      " 1   symbol        5395 non-null   object\n",
      " 2   name          5395 non-null   object\n",
      " 3   area          5380 non-null   object\n",
      " 4   industry      5380 non-null   object\n",
      " 5   cnspell       5395 non-null   object\n",
      " 6   market        5395 non-null   object\n",
      " 7   list_date     5395 non-null   object\n",
      " 8   act_name      2575 non-null   object\n",
      " 9   act_ent_type  2575 non-null   object\n",
      "dtypes: object(10)\n",
      "memory usage: 421.6+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ts_code         5395\n",
       "symbol          5395\n",
       "name            5395\n",
       "area            5380\n",
       "industry        5380\n",
       "cnspell         5395\n",
       "market          5395\n",
       "list_date       5395\n",
       "act_name        2575\n",
       "act_ent_type    2575\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pro.query('stock_basic', exchange='',list_status='L')\n",
    "data.info()\n",
    "data.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5395"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:Wyc200504258!@localhost/stock_basic')\n",
    "\n",
    "data.to_sql('stock_basic', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 000001.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000001.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 000002.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000002.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 000004.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000004.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 000006.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000006.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 000007.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000007.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 000008.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000008.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7 000009.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000009.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8 000010.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000010.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9 000011.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000011.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 000012.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000012.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11 000014.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000014.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12 000016.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000016.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 000017.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000017.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14 000019.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000019.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "15 000020.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000020.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "16 000021.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000021.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "17 000025.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000025.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "18 000026.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000026.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19 000027.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000027.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20 000028.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000028.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21 000029.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000029.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "22 000030.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000030.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23 000031.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000031.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "24 000032.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000032.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "25 000034.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000034.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26 000035.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000035.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "27 000036.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000036.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "28 000037.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000037.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29 000039.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000039.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "30 000040.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000040.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31 000042.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000042.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32 000045.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000045.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "33 000048.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000048.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "34 000049.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000049.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "35 000050.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000050.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "36 000055.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000055.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "37 000056.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000056.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "38 000058.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000058.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "39 000059.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000059.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "40 000060.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000060.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "41 000061.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000061.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "42 000062.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000062.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "43 000063.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000063.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "44 000065.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000065.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "45 000066.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000066.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "46 000068.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000068.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "47 000069.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000069.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "48 000070.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000070.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "49 000078.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000078.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "50 000088.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000088.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "51 000089.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000089.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "52 000090.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000090.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "53 000096.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000096.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "54 000099.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000099.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "55 000100.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\WYC18\\AppData\\Local\\Temp\\ipykernel_29524\\4121588940.py:12: UserWarning: The provided table name '000100.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    }
   ],
   "source": [
    "stock_list = data['ts_code'].tolist()\n",
    "\n",
    "data_price=[]\n",
    "\n",
    "i=0\n",
    "for stock in data['ts_code'].tolist():\n",
    "            if i==55:\n",
    "                    break\n",
    "            i+=1\n",
    "            print(i,stock)\n",
    "            data_price=pro.daily(ts_code=stock)\n",
    "            data_price.to_sql(stock, con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   count(*)\n",
      "0      5395\n",
      "     ts_code  symbol  name area industry cnspell market list_date act_name  \\\n",
      "0  000009.SZ  000009  中国宝安   深圳     电气设备    zgba     主板  19910625   无实际控制人   \n",
      "\n",
      "  act_ent_type  \n",
      "0            无  \n"
     ]
    }
   ],
   "source": [
    "sql = \"select count(*) from stock_basic.stock_basic\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)\n",
    "\n",
    "sql = \"select * from stock_basic.stock_basic where name='中国宝安'\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         ts_code trade_date   open   high    low  close  pre_close  change  \\\n",
      "0      000021.SZ   20250124  19.33  19.75  19.30  19.70      19.48    0.22   \n",
      "1      000021.SZ   20250123  20.03  20.42  19.48  19.48      19.86   -0.38   \n",
      "2      000021.SZ   20250122  19.73  20.05  19.60  19.86      19.72    0.14   \n",
      "3      000021.SZ   20250121  19.65  19.79  19.23  19.72      19.78   -0.06   \n",
      "4      000021.SZ   20250120  18.60  20.12  18.42  19.78      18.29    1.49   \n",
      "...          ...        ...    ...    ...    ...    ...        ...     ...   \n",
      "11995  000021.SZ   19991129  23.31  23.83  23.20  23.24      23.50   -0.26   \n",
      "11996  000021.SZ   19991126  23.97  23.98  23.39  23.50      23.58   -0.08   \n",
      "11997  000021.SZ   19991125  23.50  23.70  23.30  23.58      23.61   -0.03   \n",
      "11998  000021.SZ   19991124  24.15  24.15  23.58  23.61      24.15   -0.54   \n",
      "11999  000021.SZ   19991123  23.96  24.55  23.88  24.15      23.86    0.29   \n",
      "\n",
      "       pct_chg         vol        amount  \n",
      "0       1.1294   507564.54  9.928451e+05  \n",
      "1      -1.9134   725892.32  1.446954e+06  \n",
      "2       0.7099   809560.73  1.606095e+06  \n",
      "3      -0.3033  1049398.26  2.045921e+06  \n",
      "4       8.1465  1512327.08  2.952619e+06  \n",
      "...        ...         ...           ...  \n",
      "11995  -1.1100     4106.00  9.594236e+03  \n",
      "11996  -0.3400     6587.00  1.547848e+04  \n",
      "11997  -0.1300     6172.00  1.446210e+04  \n",
      "11998  -2.2400     6881.00  1.636974e+04  \n",
      "11999   1.2200     7757.00  1.864610e+04  \n",
      "\n",
      "[12000 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "sql = \"select * from stock_basic.`000021.sz`\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
